<?php

/* @file QTree.php
 * @date 2009-11-23
 * @modify 2010.0.09 v0.0.0.2
 */


class KD_QTree{

    // <<$model>>
    protected $model;
    // -- end -- <<$model>>

    // <<$setting>>
      protected $setting = array(

          /** 连接方式: inner 表示内部生成, outer表示外部引用*/
          'DBConnection'  =>  'inner', 

          'DBWrap'    =>  'KD_Model',

          'DBInfo'    =>  null,

          'dbms'      =>  'mysql',    

          //树所在的数据表
          'table'     =>  'qtree',

          //主键
          'key'       =>  'id',

          //前值
          'pre'      =>  'pre',

          //后值
          'succ'     =>  'succ',

          //标题
          'title'     =>  'title',

          //父节点外键
          'parentKey' =>  'parent_id',   
      );
    // -- end -- <<$setting>>

    // <<query()>>
      private function query($sql){
        $dbms = $this->setting['dbms'];
        $record = $this->model->query( array_key_exists($dbms, $sql) ? $sql[$dbms] : $sql['default'] );
        if(is_array($record)){
          return $record;  
        }
        elseif($record === false){
          return false;
        }
        elseif(method_exists($record, 'fetchAll')){
          $result = $record->fetchAll();
          return $result;
        }
        else{
          return false;
        }
      }
      // -- end -- <<query()>>

    // <<__construct()>>
      public function __construct($setting = null){
        if( is_array($setting) ){
          if(array_key_exists('dbms', $setting)){
            $this->setting = array_merge($this->setting, $setting);  
          }
          elseif( array_key_exists('DBInfo', $setting) && array_key_exists('dbms', $setting['DBInfo']) ){
            $this->setting = array_merge($this->setting,$setting, array('dbms'=> $setting['DBInfo']['dbms']) );
          }
          if($this->setting['DBConnection'] === 'inner'){
            $this->model = new $this->setting['DBWrap']($this->setting['DBInfo']);
          }else{
            $this->model = $this->setting['DBWrap'];  
          }
        }
      }
    // -- end -- <<__construct()>>

    // <<isNode()>>
      public function isNode($node){

        extract($this->setting);

        $result =   is_array($node) &&
                    array_key_exists($key, $node) && 
                    array_key_exists($title, $node) && 
                    array_key_exists($pre, $node) &&  
                    array_key_exists($succ, $node) &&  
                    array_key_exists($parentKey, $node) ? true : false ;  

        return $result;

      }
    // -- end -- <<isNode()>>

    // <<isLeaf>>
    public function isLeaf($node){
        extract($this->setting);

        return ( $node[succ] - $node[pre] === 1 ) ? true :false ;
    }
    // -- end -- <<isLeaf>>

    // <<getNode()>>
      /**
       * @return node|false, false表示无,id错误.
       */
      public function getNode($inputKey){

        extract($this->setting);

        $sql = array(
            'mysql'   => sprintf('SELECT * FROM `%s` WHERE `%s`=%u LIMIT 1;', $table, $key, $inputKey),
            'sqlite2' => sprintf('SELECT * FROM [%s] WHERE [%s]=%u LIMIT 1;', $table, $key,  $inputKey),
            'default' => sprintf('SELECT * FROM %s WHERE %s=%u LIMIT 1;', $table, $key, $inputKey ),
            );
        
        //2010.03.05 jun<kenlixin@gmail.com> modify
        /* old code
        $record =   $this->model->query( array_key_exists($dbms,$sql) ? $sql[$dbms] : $sql['default']);

        $result =   ($record === false) ? false : $record->fetch();
        */

        //new code

        $record = $this->query($sql);
        $result = ($record === false) ? false : $record[0];

        //----modify end------------------

        return $result;
      }
    // -- end -- <<getNode()>>

    // <<getRootNode()>>
      public function getRootNode(){

        extract($this->setting);


        $sql = array(
            'mysql'    => sprintf('SELECT * FROM `%s` WHERE `%s` = ( SELECT MAX(`%2$s`) FROM `%1$s` ) LIMIT 1;', $table, $succ),
            'sqlite2'  => sprintf('SELECT * FROM [%s] WHERE [%s] = ( SELECT MAX([%2$s]) FROM [%1$s] ) LIMIT 1;', $table, $succ),
            'default'  => sprintf('SELECT * FROM %s WHERE %s = ( SELECT MAX(%2$s) FROM %1$s ) LIMIT 1;', $table, $succ),
            );
        
        //2010.03.05 jun<kenlixin@gmail.com> modify 
        /*old code
        $record = $this->model->query( array_key_exists($dbms, $sql) ? $sql[$dbms] : $sql['default'] );
        $result = ($record === false) ? false : $record->fetch();
        */
        //new code
        $record = $this->query($sql);
        $result = ($record === false) ? false : $record[0];
        //----modify end-----------------------
        return $result;
      }
    // -- end -- <<getRootNode()>>

    // <<getICNodes()>>
      public function getICNodes($node){

        extract($this->setting);

        $sql = array(
          'mysql'   => sprintf('SELECT * FROM `%s` WHERE `%s` = %u ;', $table, $parentKey, $node[$key]),
          'sqlite2' => sprintf('SELECT * FROM [%s] WHERE [%s] = %s ;', $table, $parentKey, $node[$key]),
          'default' => sprintf('SELECT * FROM %s WHERE %s = %u ;', $table, $parentKey, $node[$key]),
        );

        //2010.03.05 jun<kenlixin@gmail.com> modify 
        /*old code
          $record = $this->model->query(( array_key_exists($dbms, $sql) ) ? $sql[$dbms] : $sql['default'] ) ;
          $result = ( $record === false ) ? false : $record->fetchAll();
         */
        //new code
        $result = $this->query($sql);
        //modify end--------------------
        return $result;

      }
    // -- end -- <<getICNodes()>>

    // <<getSLNodes()>>
      public function getSLNodes($node){

        //参数不符合条件,返回null
        if($this->isNode($node) === false){return null;}

        extract($this->setting);

        //无父节点,返回 false
        if( empty($node[$parentKey]) ){ return false; }

        //SELECT * FROM table WHERE id IN ( SELECT id FROM table WHERE parentid = $node['parentid'])  ORDER BY pre ASC;
        $sql = array(
          'mysql'   =>  sprintf('SELECT * FROM `%s` WHERE `%s` IN ( SELECT `%2$s` FROM `%1$s` WHERE `%s` = %u) ORDER BY `%s` ASC;', $table, $key, $parentKey, $node[$parentKey], $pre),
          'sqlite2' =>  sprintf('SELECT * FROM [%s] WHERE [%s] IN ( SELECT [%2$s] FROM [%1$s] WHERE [%s] = %u) ORDER BY [%s] ASC;', $table, $key, $parentKey, $node[$parentKey], $pre),
          'default' =>  sprintf('SELECT * FROM %s WHERE %s IN ( SELECT %2$s FROM %1$s WHERE %s = %u) ORDER BY %s ASC;', $table, $key, $parentKey, $node[$parentKey], $pre),
        );

        $record = $this->model->query( array_key_exists($dbms, $sql) ? $sql[$dbms] : $sql['defalut'] );

        $result = ($record === false) ? false : $record->fetchAll();

      }
    // -- end -- <<getSLNodes()>>

    // <<getAllNodes()>>
      public function getAllNodes(){

          extract($this->setting);

          $sql  = array(
              'mysql'   => sprintf( 'SELECT * FROM `%s` ORDER BY `%s` ASC;', $table, $pre ),
              'sqlite2' => sprintf( 'SELECT * FROM [%s] ORDER BY [%s] ASC;', $table, $pre ),
              'default' => sprintf( 'SELECT * FROM %s ORDER BY %s ASC;', $table, $pre ),
          );

          $record = $this->model->query( array_key_exists($dbms,$sql) ? $sql[$dbms] : $sql['default'] );

          $result     =   ($record === false) ? false : $record->fetchAll();
          return $result;
      }
    // -- end -- <<getAllNodes()>>

    // <<getFCNode()>>
      public function getFCNode($node){

        if($this->isNode($node) == false){return null;}

        extract($this->setting);

        $sql = array(
          'mysql'   =>  sprintf('SELECT * FROM %s WHERE %s = %u LIMIT 1 ;', $table, $pre, $node[$pre]+1),
          'sqlite2' =>  sprintf('SELECT * FROM %s WHERE %s = %u LIMIT 1 ;', $table, $pre, $node[$pre]+1),
          'default' =>  sprintf('SELECT * FROM %s WHERE %s = %u LIMIT 1 ;', $table, $pre, $node[$pre]+1),
        );

        $record = $this->model->query( (array_key_exists($dbms, $sql)) ? $sql[$dbms]  : $sql['default'] );

        $result = ($record === false) ? false : $record->fetch();

        return $result;
      }
    // -- end -- <<getFCNode()>>

    // <<getSubNodes()>>
      public function getSubNodes($node, $offset=0, $maxrows=5000){

        if($this->isNode($node) == false){return null;}

        extract($this->setting);

        $flag1 = $node[$pre];
        $flag2 = $node[$succ];


        $sql = array(

            'mysql'   => sprintf('SELECT * FROM `%s` WHERE `%s` BETWEEN %u AND %u  ORDER BY `%2$s` ASC LIMIT %u, %u ;', $table, $pre, $flag1, $flag2, $offset, $maxrows),

            'sqlite2' => sprintf('SELECT * FROM [%s] WHERE [%s] BETWEEN %u AND %u  ORDER BY [%2$s] ASC LIMIT %u, %u ;', $table, $pre, $flag1, $flag2, $offset, $maxrows),

            'default' => sprintf('SELECT * FROM %s WHERE %s BETWEEN %u AND %u  ORDER BY %2$s ASC LIMIT %u, %u ;', $table, $pre, $flag1, $flag2, $offset, $maxrows),
        );


        $record = $this->model->query( array_key_exists($dbms, $sql) ? $sql[$dbms] : $sql['default'] );

        $result = ($record === false) ? false : $record->fetchAll(); 

        return $result;

      }
    // -- end -- <<getSubNodes()>>

    // <<getNodePath()>>
      public function getNodePath($node){

        if($this->isNode($node) == false){return null;}

        extract($this->setting);


        $flag1   =   $node[$pre];
        $flag2   =   $node[$succ];


        $sql = array(
            'mysql'   => sprintf('SELECT * FROM `%s` WHERE `%s` < %u AND `%s` > %u ORDER BY `%2$s` ASC ;', $table, $pre, $flag1, $succ, $flag2),
            'sqlite2' => sprintf('SELECT * FROM [%s] WHERE [%s] < %u AND [%s] > %s ORDER BY [%2$s] ASC ;', $table, $pre, $flag1, $succ, $flag2),
            'default' => sprintf('SELECT * FROM %s WHERE %s < %u AND %s > %u ORDER BY %2$s ASC ;', $table, $pre, $flag1, $succ, $flag2),
            );

        $record = $this->model->query( array_key_exists($dbms, $sql) ? $sql[$dbms] : $sql['default'] );
        $result = ($record === false) ? false : $record->fetchAll();
        return $result;    
      }
    // -- end -- <<getNodePath()>>

    // <<getParentNode()>>
      public function getParentNode($node){

        if($this->isNode($node) == false){ return null;}

        extract($this->setting);

        $sql = array(
            'mysql'   => sprintf('SELECT * FROM %s WHERE %s = (SELECT %s FROM %1$s WHERE %2$s = %s ) LIMIT 1;', $table, $key, $parentKey, $node[$key] ),
            'sqlite2' => sprintf('SELECT * FROM %s WHERE %s = (SELECT %s FROM %1$s WHERE %2$s = %s ) LIMIT 1;', $table, $key, $parentKey, $node[$key] ),
            'default' => sprintf('SELECT * FROM %s WHERE %s = (SELECT %s FROM %1$s WHERE %2$s = %s ) LIMIT 1;', $table, $key, $parentKey, $node[$key] ),
            );

        $record = $this->model->query( array_key_exists($dbms,$sql) ? $sql[$dbms] : $sql['default'] );

        $result = ($record === false) ? false : $record->fetch();

        return $result;
      }
    // -- end -- <<getParentNode()>>

    // <<getChildCount()>>
      public function getChildCount($node){
          if( $this->isNode($node) === false ){
              return null;
          }

          extract($this->setting);
          return ( $node[$succ] - $node[$pre] - 1 )/2;
      }
    // -- end -- <<getChildCount()>>

    // <<displayTree()>>
      public function displayTree(){

          return $this->displaySubTree($this->getRootNode()); 
      }
    // -- end -- <<displayTree()>>

    // <<displaySubTree()>>
      public function displaySubTree($node){
        if($this->isNode($node) === false){return null;}

        extract($this->setting);

        $result = array();
        $stack = array();

        $nodes = $this->getSubNodes($node); 

        foreach($nodes as $row){
          if( count($stack)>0 ) { while( $stack[count($stack)-1] < $row[$succ] ){ array_pop($stack); } }
          $result[] = str_repeat('--', count($stack)) . $row[$title];
          $stack[]    =   $row[$succ];
        }

        return $result;
      }
    // -- end -- <<displaySubTree()>>

    // <<displaySubTreeHtml()>>
      public function displaySubTreeHtml($node){
        if($this->isNode($node == false)){return null;}

        extract($this->setting);

        $result = '';
        $stack = array();

        $nodes = $this->getSubNodes($node); 

        foreach($nodes as $row){
          if( count($stack)>0 ) { while( $stack[count($stack)-1] < $row[$succ] ){ $result .='</ul>';array_pop($stack); } }
          $result     .= "<ul><li><div>{$row[$title]}</div></li>";
          $stack[]    =   $row[$succ];
        }

        return $result;
      }
    // -- end -- <<displaySubTreeHtml()>>

    // <<insertNode()>>
      public function insertNode($targetNode, $referNode, $position='below'){

        $check_param = is_array($targetNode) && 
          ( $position == 'after' || $position == 'before' || $position == 'below' ) &&
          $this->isNode($referNode) ? true : false;

        if($check_param === false){return null;}

        //参考节点为根节点时特殊处理

        $root = $this->getRootNode();

        if( ($referNode == $root) && ($position !== 'below') ){ return null; }

        extract($this->setting);

        switch($position){

          case 'after':

            $flag_update  = $referNode[$succ];
            $flag_insert  = $referNode[$succ];
            $flag_pkey    = $referNode[$parentKey];

            break;

          case 'before':

            $flag_update  = $referNode[$pre]-1;
            $flag_insert  = $flag_update;
            $flag_pkey    = $referNode[$parentKey];

            break;

          case 'below':

            $flag_update  = $referNode[$succ]-1;
            $flag_insert  = $flag_update;
            $flag_pkey    = $referNode[$key]; 

            break;
        }


        $sqlUpdatePre = array(
            'mysql'   => sprintf('UPDATE `%s` SET `%s` = `%2$s` + 2 WHERE `%2$s` > %u ;', $table, $pre, $flag_update ),
            'sqlite2' => sprintf('UPDATE [%s] SET [%s] = [%2$s] + 2 WHERE [%2$s] > %u ;', $table, $pre, $flag_update ),
            'default' => sprintf('UPDATE %s SET %s = %2$s + 2 WHERE %2$s > %u ;', $table, $pre, $flag_update ),
            );


        $sqlUpdateSucc = array(
            'mysql'   =>  sprintf('UPDATE `%s` SET `%s` = `%2$s` + 2 WHERE `%2$s` > %u ;', $table, $succ, $flag_update),
            'sqlite2' =>  sprintf('UPDATE [%s] SET [%s] = [%2$s] + 2 WHERE [%2$s] > %u ;', $table, $succ, $flag_update),
            'default' =>  sprintf('UPDATE %s SET %s=%2$s+2 WHERE %2$s > %u ;', $table, $succ, $flag_update),
            );


        $targetNode[$pre]       = $flag_insert + 1;
        $targetNode[$succ]      = $flag_insert + 2;
        $targetNode[$parentKey] = $flag_pkey ;

        $fields = array_keys($targetNode) ;

        $values = array_values($targetNode);

        $sqlInset = null;

        switch($dbms){

          case 'mysql':

            $sqlInsert = "INSERT INTO `$table` (";

            foreach( $fields as $fields_key=>$fields_value ) { $fields[$fields_key] = "`$fields_value`"; }

            foreach( $values as $values_key=>$values_value ) { (is_string($values_value)) && (!is_numeric($values_value)) && $values[$values_key] = "'$values_value'"; }

            break;

          case 'sqlite2':

            $sqlInsert = "INSERT INTO [$table] (";

            foreach( $fields as $fields_key=>$fields_value ) { $fields[$fields_key] = "[$fields_value]"; }

            foreach( $values as $values_key=>$values_value ) { (is_string($values_value)) && (!is_numeric($values_value)) && $values[$values_key] = "'$values_value'"; }

            break;

          default:

            $sqlInsert = "INSERT INTO `$table` (";

            foreach( $fields as $fields_key=>$fields_value ) { $fields[$fields_key] = "[$fields_value]"; }

            foreach( $values as $values_key=>$values_value ) { (is_string($values_value)) && (!is_numeric($values_value)) && $values[$values_key] = "'$values_value'"; }

            break;
        }

        $fields = implode(',', $fields);

        $values = implode(',', $values);

        $sqlInsert .= $fields . ') VALUES (' . $values . ');';

        //return $sqlUpdatePre[$dbms]  .  $sqlUpdateSucc[$dbms]  .  $sqlInsert;

        try{
          $this->model->startTrans();
          $this->model->execute( (array_key_exists($dbms, $sqlUpdatePre)) ? $sqlUpdatePre[$dbms]: $sqlUpdatePre['default']);
          $this->model->execute( (array_key_exists($dbms, $sqlUpdateSucc)) ? $sqlUpdateSucc[$dbms] : $sqlUpdateSucc['default'] );
          $this->model->execute($sqlInsert);
          $this->model->commit();
          return true;
        }catch(PDOException $e){   
          throw $e;
          return null;
        }
      }
    // -- end -- <<insertNode()>>

    // <<deleteNode()>>
      public function deleteNode($node){

        if($this->isNode($node) == false){return null;}

        extract($this->setting);

        $flag_pre     =   $node[$pre];
        $flag_succ    =   $node[$succ];
        $flag_width   =   $flag_succ - $flag_pre + 1 ;



        $sqlDelete = array(
            'mysql'   =>  sprintf('DELETE FROM `%s` WHERE `%s` BETWEEN %u AND %u ;', $table, $pre, $flag_pre, $flag_succ),
            'sqlite2' =>  sprintf('DELETE FROM [%s] WHERE [%s] BETWEEN %u AND %u ;', $table, $pre, $flag_pre, $flag_succ),
            'default' =>  sprintf('DELETE FROM %s WHERE %s BETWEEN %u AND %u ;', $table, $pre, $flag_pre, $flag_succ),
            );


        $sqlUpdatePre = array(
            'mysql'   =>  sprintf('UPDATE `%s` SET `%s` = `%2$s` - %u WHERE `%2$s` > %u ;', $table, $pre, $flag_width, $flag_pre),
            'sqlite2' =>  sprintf('UPDATE [%s] SET [%s] = [%2$s] - %u WHERE [%2$s] > %u ;', $table, $pre, $flag_width, $flag_pre),
            'default' =>  sprintf('UPDATE %s SET %s = %2$s - %u WHERE %2$s > %u ;', $table, $pre, $flag_width, $flag_pre),
            );


        $sqlUpdateSucc = array(
            'mysql'   =>  sprintf('UPDATE `%s` SET `%2$s` = `%2$s` - %3$u WHERE `%2$s` > %4$u ;', $table, $succ, $flag_width, $flag_succ),
            'sqlite2' =>  sprintf('UPDATE [%s] SET [%2$s] = [%2$s] - %3$u WHERE [%2$s] > %4$u ;', $table, $pre, $succ, $flag_width, $flag_succ),
            'default' =>  sprintf('UPDATE %s SET %2$s = %2$s - %3$u WHERE %2$s > %4$u ;', $table, $pre, $succ, $flag_width, $flag_succ),
            );

        try{
          $this->model->startTrans();
          $this->model->execute( (array_key_exists($dbms, $sqlDelete))      ? $sqlDelete[$dbms]     : $sqlDelete['default']     );
          $this->model->execute( (array_key_exists($dbms, $sqlUpdatePre))   ? $sqlUpdatePre[$dbms]  : $sqlUpdatePre['default']  );
          $this->model->execute( (array_key_exists($dbms, $sqlUpdateSucc))  ? $sqlUpdateSucc[$dbms] : $sqlUpdateSucc['default'] );
          $this->model->commit();
          return true;
        }catch(PDOException $e){
          throw $e;
          return false;
        }
      }
    // -- end -- <<deleteNode()>>

    // <<deleteAllNode()>>
      public function deleteAllNode(){
        extract($this->setting);

        $sql = array(
            'mysql'   => "DELETE FROM `$table` ;",
            'sqlite2' => "DELETE FROM [$table] ;",
            'default' => "DELETE FROM $table ;",
            );

        try{
          $this->model->execute( (array_key_exists($dbms, $sql)) ? $sql[$dbms] : $sql['default'] );
          return true;
        }catch(PDOException $e){
          throw $e;
          return false;
        }
      }
    // -- end -- <<deleteAllNode()>>

    // <<updateNode()>>
    public function updateNode($id,$newTitle,$anothInfoArray){
          //处理存入的其他信息数组$anothInfoArray
          $arrayKey=array_keys($anothInfoArray);

          $arrayValues=array_values($anothInfoArray);

          $countValues=count($arrayValues);

          //生成需要的SQL语句片段,兼容Sqlite和mysql
          $str = '';

          for($i=0;$i<$countValues;$i++){

            $str.=",{$arrayKey[$i]}='{$arrayValues[$i]}'";

          }

          extract($this->setting);
          $sql = array(
              'mysql'   =>  sprintf('UPDATE `%s` SET `%s` = \'%s\' %s WHERE `%s` = \'%u\';', $table, $title, $newTitle, $str,$key,$id),
              'sqlite2' =>  sprintf('UPDATE [%s] SET %s = \'%s\' %s WHERE %s = \'%u\';', $table, $title, $newTitle, $str,$key,$id),
              'default' =>  sprintf('UPDATE %s SET %s = %s %s WHERE %s = %u;', $table, $title, $newTitle, $str,$key,$id),
              );
          try{
            $this->model->execute( (array_key_exists($dbms, $sql)) ? $sql[$dbms] : $sql['default'] );
            return true;
          }catch(PDOException $e){
            throw $e;
            return false;
          }

        }
    // -- end -- <<updateNode()>>

    // <<moveNode()>>
      public function moveNode($targetNode, $referNode, $position='after'){}
    // -- end -- <<moveNode()>>

    // <<init()>>
    public function init(){
          extract($this->setting);
          $sql = array(
              'mysql'   =>  sprintf('INSERT INTO `%s` ( `%s`, `%s`, `%s` ) VALUES (\'%s\',1,2) ;', $table, $title,$pre,$succ, '_virtual_root_'),
              'sqlite2' =>  sprintf('INSERT INTO [%s] ( [%s], [%s], [%s] ) VALUES (\'%s\',1,2) ;', $table, $title,$pre,$succ, '_virtual_root_'),
              'default' =>  sprintf('INSERT INTO %s ( %s, %s, %s ) VALUES (\'%s\',1,2) ;', $table, $title,$pre,$succ, '_virtual_root_'),
              );
          try{
            $this->model->execute( (array_key_exists($dbms, $sql)) ? $sql[$dbms] : $sql['default'] );
            return true;
          }catch(PDOException $e){
            throw $e;
            return false;
          }

    }
    // -- end -- <<init()>>

}
